#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import pymysql
import datetime
import xlwt
from settings import my_settings
settings = my_settings.mysqlSettings(hostname='96_mysql')
settings = settings.mysql_conf
MYSQL_HOST = settings['MYSQL_HOST']
MYSQL_PORT = settings['MYSQL_PORT']
MYSQL_DB = settings['MYSQL_DB']
MYSQL_USER = settings['MYSQL_USER']
MYSQL_PASSWD = settings['MYSQL_PASSWD']
MYSQL_CHARTSET = settings['MYSQL_CHARTSET']

def get_data(sub_days):
    """
    :param sub_days: 减去的日期，前sub_days的数据
    :return:
    """
    db = pymysql.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        database=MYSQL_DB,
        user=MYSQL_USER,
        password=MYSQL_PASSWD,
        charset=MYSQL_CHARTSET)
    cursor = db.cursor()

    yesterday_date = datetime.datetime.now().date() + datetime.timedelta(days=-sub_days)
    yesterday_date_str = str(yesterday_date)
    sql = "select date,fund,company,jybchnname,jybcode,direction,shares_ark,tradevalue_ark,wgtpct_ark from ark_trade " \
          "where date='%s' and fund!='COMBINED' order by fund asc,direction asc,wgtpct_ark desc" % (yesterday_date_str)
    cursor.execute(sql)
    datas = cursor.fetchall()

    # for data in datas:
    #     print(data)
    cursor.close()
    db.close()

    return datas

def export_excel(datas,sub_days):
    """
    :param datas: ((),(),())从数据库导出的元祖类型list
    :param sub_days: 减去的日期，前sub_days的数据
    :return:
    """
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet('Sheet1')

    # 写入excel
    # 参数对应 行, 列, 值
    worksheet.write(0, 0, label='日期')
    worksheet.write(0, 1, label='ARK ETF名')
    worksheet.write(0, 2, label='原始数据公司名')
    worksheet.write(0, 3, label='交易宝内的公司名')
    worksheet.write(0, 4, label='交易宝code')
    worksheet.write(0, 5, label='ARK主动交易买卖方向')
    worksheet.write(0, 6, label='ARK主动交易 买卖标的 股数')
    worksheet.write(0, 7, label='ARK主动交易 买卖标的 金额')
    worksheet.write(0, 8, label='ARK主动交易 买卖标的 金额占比')
    for i in range(len(datas)):
        data=datas[i]
        row=i+1
        worksheet.write(row, 0, label=str(data[0]))
        worksheet.write(row, 1, label=str(data[1]))
        worksheet.write(row, 2, label=str(data[2]))
        worksheet.write(row, 3, label=str(data[3]))
        worksheet.write(row, 4, label=str(data[4]))
        worksheet.write(row, 5, label=str(data[5]))
        worksheet.write(row, 6, label=str(data[6]))
        worksheet.write(row, 7, label=str(data[7]))
        worksheet.write(row, 8, label=str(data[8]))
    # 保存
    yesterday_date = datetime.datetime.now().date() + datetime.timedelta(days=-sub_days)
    yesterday_date_str = str(yesterday_date)
    Excel_filename='ark_data_'+yesterday_date_str+'.xls'
    workbook.save(Excel_filename)

if __name__=='__main__':
    today_weekday = datetime.datetime.now().weekday()#0,1,2,3,4,5,6
    if today_weekday==0:
        sub_days=3
    else:
        sub_days=1
    datas=get_data(sub_days)
    export_excel(datas,sub_days)